<?php
/*********************************************
 *  CPG Dragonfly™ CMS
 *********************************************
	Copyright © since 2010 by CPG-Nuke Dev Team
	http://dragonflycms.org

	Dragonfly is released under the terms and conditions
	of the GNU GPL version 2 or any later version
*/

namespace Poodle\SQL;

abstract class XML
{
	public
		$queries,
		$prefix,
		$errors;

	protected
		$SQL,
		$platform,
		$tables;

	protected static
		$onDuplicateActions = array('ERROR','IGNORE','UPDATE'),
		$dataModes = array('ON-EMPTY','ON-UPDATE','IDENTICAL','UNIQUE');

	function __construct(\Poodle\SQL $SQL)
	{
		$this->SQL = $SQL;
		$this->prefix = $this->SQL->TBL->prefix;
		$this->platform = strtolower($SQL->engine);
		$this->tables   = $SQL->listTables();
	}

	public function importDataFromFile($file)
	{
		if (!$this->validateFile($file))
		{
			return false;
		}
		return $this->importDataFromXML(simplexml_load_file($file/*, null, LIBXML_NOCDATA*/));
	}

	public function importDataFromString($str)
	{
		if (!$this->validateString($str))
		{
			return false;
		}
		return $this->importDataFromXML(simplexml_load_string($str/*, null, LIBXML_NOCDATA*/));
	}

	public function importDataFromXML($xml)
	{
		$this->queries = array();
		foreach ($xml->table as $table)
		{
			$name = $this->prefix.$table['name'];
			if (in_array($name,$this->tables)) $this->importTableData($table, $name);
		}
		$this->importQueries($xml->sql);
		return true;
	}

	public function syncSchemaFromFile($file)
	{
		if (!$this->validateFile($file)) { return false; }
		return $this->syncSchemaFromXML(simplexml_load_file($file/*, null, LIBXML_NOCDATA*/));
	}

	public function syncSchemaFromString($str)
	{
		if (!$this->validateString($str)) { return false; }
		return $this->syncSchemaFromXML(simplexml_load_string($str/*, null, LIBXML_NOCDATA*/));
	}

	abstract public function syncSchemaFromXML($xml);

	public function validateFile($file)  { return $this->validate($file); }
	public function validateString($str) { return $this->validate($str, true); }
	public function validate($file, $file_is_data=false)
	{
		libxml_use_internal_errors(true);
		$DOM = new \DomDocument();
		if ($file_is_data) {
			$DOM->loadXML($file);
		} else {
			$DOM->load($file);
		}
		if (!$DOM->schemaValidate(__DIR__.'/xml/schema.xsd')) {
			$this->errors = libxml_get_errors();
			return false;
		}
		return true;
	}

	protected function validPlatform(\SimpleXMLElement $item)
	{
		return (!$item['platform'] || $this->platform == strtolower($item['platform']));
	}

	protected function importQueries($sql)
	{
		if ($sql && $sql->query)
			foreach ($sql->query as $query)
				if ($this->validPlatform($query))
					$this->queries[] = trim(preg_replace('#{([a-z0-9_]+)}#s', $this->prefix.'$1', $query));
	}

	protected function importTableData($table, $name=null)
	{
		if ($table->tr)
		{
			$name = $name ? $name : $this->prefix.$table['name'];
			try {
				$update = $this->SQL->count(substr($name,strlen($this->prefix)));
			} catch (\Exception $e) { $update = false; }

			if ('ON-EMPTY'==$table['datamode'] && $update) {
				return;
			}
			if ('ON-UPDATE'==$table['datamode'] && !$update) {
				return;
			}

			$def_mode = (int)array_search($table['onduplicate'], self::$onDuplicateActions);
			$cols = array();
			foreach ($table->col as $col) $cols[] = (string)$col['name'];
			foreach ($table->tr as $record)
			{
				$mode = isset($record['onduplicate']) ? (int)array_search($record['onduplicate'], self::$onDuplicateActions) : $def_mode;
				$row = array();
				if ($record->td) {
					// <tr><td></td><td></td></tr>
					$i = 0;
					foreach ($record->td as $col) {
						$v = trim((string)$col);
						$xsi = $col->attributes('http://www.w3.org/2001/XMLSchema-instance'); // xsi
						$row[$cols[$i++]] = $xsi['nil'] ? 'NULL' : ($col['israw'] ? $v : $this->SQL->quote($v));
					}
				} else {
					// <tr><colname1></colname1><colname2></colname2></tr>
					foreach ($record->children() as $k => $col) {
						$v = trim((string)$col);
						$xsi = $col->attributes('http://www.w3.org/2001/XMLSchema-instance'); // xsi
						$row[$k] = $xsi['nil'] ? 'NULL' : ($col['israw'] ? $v : $this->SQL->quote($v));
					}
				}
				$duplicate = '';
				$ignore = (1==$mode && $update)?'IGNORE':'';
				if (2==$mode && $update) {
					$duplicate = 'ON DUPLICATE KEY UPDATE';
					foreach ($row as $k => $v) $duplicate .= " $k=$v,";
					$duplicate = substr($duplicate,0,-1);
				}
				$this->queries[] = "INSERT {$ignore} INTO {$name} (".implode(',',array_keys($row)).") VALUES (".implode(',',$row).") {$duplicate}";
			}
		}
	}

	public function getDocFoot() { return '</database>'; }
	public function getDocHead()
	{
		return '<?xml version="1.0"?>
<database version="1.0" name="'.$this->SQL->database.'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">';
	}

	public function getTableDataXML($name, $table, array $config=array())
	{
		$cols = array_keys($this->SQL->listColumns($table));

		// Sort on max first 6 columns, else might have error: Out of sort memory; increase server sort buffer size
		$cc = min(6,count($cols));
		$order = array();
		for ($i=1;$i<=$cc;++$i) $order[] = $i;

		$data   = '';
		$result = $this->SQL->query("SELECT ".implode(',',$cols)." FROM {$table} ORDER BY ".implode(',',$order));
		if ($result->num_rows) {
			$data .= "\n\n\t".'<table name="'.$name.'"';
			if (isset($config['onduplicate']) && in_array($config['onduplicate'], self::$onDuplicateActions)) {
				$data .= ' onduplicate="'.$config['onduplicate'].'"';
			}
			if (isset($config['datamode']) && in_array($config['datamode'], self::$dataModes)) {
				$data .= ' datamode="'.$config['datamode'].'"';
			}
			$data .= '>';

			# columns
			$data .= "\n\t\t<col name=\"".implode("\"/>\n\t\t<col name=\"",$cols)."\"/>";
			//$data .= "\n\t\t<col name=\"{$name}\"/>";

			while ($r = $result->fetch_row()) {
				foreach ($r as $k => $v) {
					if (is_null($v)) {
						$r[$k] = '<td xsi:nil="true"/>';
					} else if ($v && 'MySQL'===$this->SQL->engine && is_binary($v)) {
						$r[$k] = '<td israw="true">0x'.bin2hex($v).'</td>';
					} else {
//						if (false !== strpbrk($v,'&<>')) { slower ???
						if (false!==strpos($v,'&') || false!==strpos($v,'<') || false!==strpos($v,'>')) {
							$v = '<![CDATA['.$v.']]>';
						}
						$r[$k] = "<td>{$v}</td>";
					}
//					$r[$k] = htmlspecialchars($v,ENT_NOQUOTES,'UTF-8',false);
				}
				$data .= "\n\t\t<tr>" . implode('',$r) . "</tr>";
			}

			return $data."\n\t".'</table>';
		}
	}

	public function exportData($stream=null, array $config=array())
	{
//		$fp = $stream ? $stream : fopen('php://memory:16777216','rw');

		$re = '#^'.$this->prefix.'(.+)$#D';
		$data = $this->getDocHead();

		if (!isset($config['skip_tables'])) {
			$config['skip_tables'] = array(
				'auth_providers_assoc',
				'auth_providers_endpoints',
				'auth_providers_nonce',
				'log',
				'sessions',
			);
		}

		foreach ($this->SQL->listTables() as $table)
		{
			if (in_array(substr($table,strlen($this->prefix)), $config['skip_tables'])
			 || !preg_match($re, $table, $t)
			) continue;

			$data .= $this->getTableDataXML($t[1], $table, $config);
			if ($stream && $data && (false !== fwrite($stream, $data))) { $data = ''; }
		}

		$data .= "\n".$this->getDocFoot();
		if ($stream && (false !== fwrite($stream, $data))) { return true; }
		return $data;
	}

	public function getFunctionXML($name, $func)
	{
		$func = $this->SQL->getFunction($func);
		$data = "\t".'<function name="'.$name.'" returns="'.$func['returns'].'">';
		foreach ($func['parameters'] as $param)
		{
			$data .= "\n\t\t".'<param name="'.$param['name'].'"'
				.($param['dir']?' direction="'.$param['dir'].'"':'')
				.($param['type']?' type="'.$param['type'].'"':'')
				.($param['length']?' length="'.$param['length'].'"':'').'/>';
		}
		return $data
			."\n\t\t".'<body><![CDATA['.$func['definition'].']]></body>'
			."\n\t".'</function>';
	}

	public function getProcedureXML($name, $proc)
	{
		$proc = $this->SQL->getProcedure($proc);
		$data = "\t".'<procedure name="'.$name.'">';
		foreach ($proc['parameters'] as $param)
		{
			$data .= "\n\t\t".'<param name="'.$param['name'].'"'
				.($param['dir']?' direction="'.$param['dir'].'"':'')
				.($param['type']?' type="'.$param['type'].'"':'')
				.($param['length']?' length="'.$param['length'].'"':'').'/>';
		}
		return $data
			."\n\t\t".'<body><![CDATA['.$proc['definition'].']]></body>'
			."\n\t".'</procedure>';
	}

	public function getTableXML($name, $table, array $info=array())
	{
		if (!$info) $info = $this->SQL->getTableInfo($table);

		$data = "\t".'<table name="'.$name.'"';
		if ($info['comment']) { $data .= ' comment="'.htmlspecialchars($info['comment']).'"'; }
		if ($info['engine'] ) { $data .= ' engine="'.htmlspecialchars($info['engine']).'"'; }
		$data .= '>';

		# columns
		foreach ($this->SQL->listColumns($table) as $name => $col)
		{
			preg_match('#([A-Z]+)(?:\s*\(([^\(\)]+)\))?(\s+BINARY)?#i',$col['type'], $m);
			$attr = array('type="'.$m[1].'"');
			if (!empty($m[2])) $attr[] = 'length="'.$m[2].'"';
			if (!empty($m[3])) $attr[] = 'binary="true"';
			if (!$col['notnull']) $attr[] = 'nullable="true"';
			if (isset($col['default'])) $attr[] = 'default="'.$col['default'].'"';
			if ($col['comment']) $attr[] = 'comment="'.htmlspecialchars($col['comment'],ENT_COMPAT,'UTF-8',false).'"';
			$data .= "\n\t\t".'<col name="'.$name.'" '.implode(' ',$attr).'/>';
		}

		# indexes
		$indexes = $this->SQL->listIndexes($table);
		ksort($indexes);
		foreach ($indexes as $name => $key)
		{
			$data .= "\n\t\t".'<key name="'.$name.'"'.($key['type']?" type=\"{$key['type']}\"":'').'>';
			foreach ($key['columns'] as $name => $v)
				$data .= "\n\t\t\t".'<col name="'.$name.'"'.(strlen($name)!=strlen($v)?' length="'.substr($v,strlen($name)+1,-1).'"':'').'/>';
			$data .= "\n\t\t".'</key>';
		}

		# foreign keys
		$re = '#^'.$this->prefix.'(.+)$#D';
		foreach ($this->SQL->listForeignKeys($table) as $name => $key)
		{
			$name = preg_replace($re, '$1', $name);
			$key['references'] = preg_replace($re, '$1', $key['references']);
			$data .= "\n\t\t<key name=\"{$name}\" type=\"FOREIGN\" references=\"{$key['references']}\" ondelete=\"{$key['ondelete']}\" onupdate=\"{$key['onupdate']}\">";
			foreach ($key['columns'] as $name => $v) {
				$data .= "\n\t\t\t<col name=\"{$name}\"";
				if ($name !== $v) { $data .= " refcolumn=\"{$v}\""; }
				$data .= "/>";
			}
			$data .= "\n\t\t</key>";
		}

		# triggers
		foreach ($this->SQL->listTriggers($table) as $name => $trigger)
		{
			$data .= "\n\t\t".'<trigger name="'.$name.'" timing="'.$trigger['timing'].'" event="'.$trigger['event'].'"><![CDATA['.$trigger['statement'].']]></trigger>';
		}

		return $data."\n\t</table>";
	}

	public function getViewXML($name, $view)
	{
		$view = $this->SQL->getView($view);
		$prfx = $this->SQL->TBL->prefix;
		if ($prfx) { $view['definition'] = preg_replace("#([^a-z_]){$prfx}([a-z0-9_]+)#si", '$1{$2}', $view['definition']); }
		$view['definition'] = preg_replace('# (from|left join) #',"\n\$1 ", preg_replace('#(^select |,)#',"\$1\n\t",$view['definition']));
		return "\t".'<view name="'.$name.'"><![CDATA['.$view['definition'].']]></view>';
	}

	public function exportSchema($stream=null)
	{
		$re = '#^'.$this->prefix.'(.+)$#D';
		$data = $this->getDocHead();

		foreach ($this->SQL->listFunctions() as $name)
		{
			if (!preg_match($re, $name, $t)) continue;
			$data .= "\n\n".$this->getFunctionXML($t[1], $name);
			if ($stream && (false !== fwrite($stream, $data))) { $data = ''; }
		}

		foreach ($this->SQL->listProcedures() as $name)
		{
			if (!preg_match($re, $name, $t)) continue;
			$data .= "\n\n".$this->getProcedureXML($t[1], $name);
			if ($stream && (false !== fwrite($stream, $data))) { $data = ''; }
		}

		foreach ($this->SQL->listTables(true) as $table => $info)
		{
			if (!preg_match($re, $table, $t)) continue;
			$data .= "\n\n".$this->getTableXML($t[1], $table, $info);
			if ($stream && (false !== fwrite($stream, $data))) { $data = ''; }
		}

		foreach ($this->SQL->listViews() as $view)
		{
			if (!preg_match($re, $view, $t)) continue;
			$data .= "\n\n".$this->getViewXML($t[1], $view);
			if ($stream && (false !== fwrite($stream, $data))) { $data = ''; }
		}

		$data .= "\n".$this->getDocFoot();
		if ($stream && (false !== fwrite($stream, $data))) { return true; }
		return $data;
	}

	public function exportTableData($table, $stream=null, array $config=array())
	{
		$name = preg_match('#^'.$this->prefix.'(.+)$#D', $table, $t) ? $t[1] : $table;
		$data = $this->getDocHead();
		$data .= $this->getTableDataXML($name, $table, $config);
		$data .= "\n".$this->getDocFoot();
		if ($stream && (false !== fwrite($stream, $data))) { return true; }
		return $data;
	}
}
